import requests
import xlrd
import pymysql
import time

# https://www.ups.com/track?loc=zh_HK&tracknum=1Z9E09Y40452513019&requester=WT/trackdetails

conn = pymysql.connect(host='192.168.2.205', port=3306, user='jinhuitao', password='jinhuitao_123456',
                       database='sunway_db', charset='utf8')
cursor = conn.cursor()


def excel_s():
    books = xlrd.open_workbook(r"D:/ups.xlsx")

    sheet = books.sheet_by_index(0)  # sheet_by_index(0)表示为ceshishujvku.xlsx中的第一个表单
    print(f"第一列的内容是:{sheet.col_values(colx=0)}")

    return sheet.col_values(colx=0)


def mysql_s(times, title, describes, Tracking_number):
    newtime=time.strftime('%Y-%m-%d %H:%M:%S')
    cursor.execute(
        "REPLACE into load_ups set newtime=%s,times=%s,title=%s,describes=%s,Tracking_number=%s",
        [newtime,times, title, describes, Tracking_number])

    conn.commit()


def run():
    for j in excel_s():
        print(j)
        url = 'https://www.ups.com/track/api/Track/GetStatus?loc=zh_HK'
        headers = {
            'authority': 'www.ups.com',
            'method': 'POST',
            'path': '/track/api/Track/GetStatus?loc=zh_HK',
            'scheme': 'https',
            'accept': 'application/json, text/plain, */*',
            'accept-encoding': 'gzip, deflate, br',
            'accept-language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
            'content-length': '58',
            'content-type': 'application/json',
            'cookie': 'X-CSRF-TOKEN=CfDJ8Jcj9GhlwkdBikuRYzfhrpLd6d7_2EpfgajHg1rUZNqZdd1LdgI0QjS-1odUQjuSlKGzWLlrN6T2EM7U5Q1-h_Hikc0PYCAlvnbPDJSF7IdAghL-oQJfP6uJYxH_TpxfVFUPckUhHheTXWKeuFEkFV4; at_check=true; CONSENTMGR=consent:true%7Cts:1641288206280; AMCVS_036784BD57A8BB277F000101%40AdobeOrg=1; s_cc=true; _gcl_au=1.1.983945321.1641288208; st_cur_page=st_track; aam_cms=segments%3D22945447%7C9626568%7C9626828; aam_uuid=54786243396233666111792538046679672005; HASSEENNOTICE=TRUE; s_vnum=1643644800768%26vn%3D2; s_invisit=true; dayssincevisit_s=Less%20than%201%20day; bm_sz=753A648612141A6A32FDF89640261607~YAAQZ8U8t9lc3BR+AQAAhx+4Jw6RiqpZoCzGyfkSg2EitU0teK+KUSCzDfA59jOAnWJkX9jQ31DCLszX6NJotMMABbfFWkFxEz10GsCsSYpDq7/q131UEksppRkWZYz/qkUOYDdR6ZFmsfIvWWSoYRZm17a0Ll44VRUG8p6KKqDHGhiAR4ypB9v0kDgI5PDLrA1IppNDLhX0eDCYU7TmHD98zhtB+wKn7kT7L9wY1UYzjfMVzWPgckLLitVh+2448DgrQCjScoTE8aYhZBWbdk+p+H+ChdarlMLQ//JwkYY=~4469047~3618630; mboxEdgeCluster=32; ak_bmsc=E98A1AB992AC73C6412AB4A96545BFCF~000000000000000000000000000000~YAAQZ8U8tyld3BR+AQAAwCS4Jw7YSTPlifYKTL5fTRH2etGqmU0f4OA0Cf7l7mg3JjapC/loNkAH650Jd77OFJAZlGltQA8IXBFoKkYTbORaI+kZya3kc8ZOlqRtAVpBFkAk7yMFpbuzi1HQHcf3GMpkIx2a40bW39037fSjbhZvRftRneGkA9idrnFZVh3tc5ZnmePGNyytMyOG325cKS2v+fz8ZgKmYYpbkh1UWKGscrR4xkaMb0FT531hhNDRHUJvsdxzVh97nspqRWxevuHapTbXhkxi592qgTIQJSDhohO7sUpvWBiShCc8+EsaKDKy+mQabfwZBRavEFSnDxJjuqF9K3y159hCWvR/T97YKnrLNcMgfDhOxvPFQPPSBrThXJBT93sHHR+eGirDmTnOhPXCGgOcQyezwGDSqBSXgZV+ETdr/YgDHi85/kc7d2y4X1Uyi8Su7si3kyBXwQZwZkjnF7auNurNS5auVanIgxC5hsgN2YQ=; AMCV_036784BD57A8BB277F000101%40AdobeOrg=-1124106680%7CMCIDTS%7C18997%7CMCMID%7C55216696863761628391749451949285559553%7CMCAAMLH-1641948687%7C11%7CMCAAMB-1641948687%7CRKhpRz8krg2tLO6pguXWp5olkAcUniQYPHaMWWgdJ3xzPWQmdj0y%7CMCOPTOUT-1641351087s%7CNONE%7CMCCIDH%7C2111192083%7CvVersion%7C5.2.0; ups_language_preference=en_US; sharedsession=a0539fcd-a098-4775-a210-dd36b640161d:w; X-XSRF-TOKEN-ST=CfDJ8Jcj9GhlwkdBikuRYzfhrpIR_U6uCs7mVZuUDQyQxmOKoGcWSzavemYf-TclpDT7cIt8TWYU3W79qj0SFLpxl52i-U5ZE7jewsTxSpOh095ELOdrM8ruQ5wjMVn_g_QSlcmAuiKKCLkIa-yV1l70Hwk; PIM-SESSION-ID=VY7G9k0oEX4hJ8T9; mbox=PC#b6a27b43fde54e70ae0b0b64f1d18095.32_0#1704588727|session#c5fa41c8d9ab40b28adc4e79b621ac3c#1641345746; _abck=63AB01564BFC3D6A0514E0B7098E288B~0~YAAQZsU8t8JQz799AQAAd5K7JwcUnnR07mqTpH9tM/9JuE0/fpF56k0+1cWWFPJFwHIbaT31keXw+0x3f7lKkSHdDYjlRzftr0Bm9jOKiVCuVo12CWYNP/a0J7OmJ28dbELqGYfJ4CWzYEOtGSQonM+EiH2hwgU1b1bXREnjxO4b7Ep4Y9A6ge1KooDpkhhYlMhEpS5ImJ+M67ReSkanCLKPqGsZuOm/OFRi1Je9dD+ikfYnZNodSWkf63zsJVdNkS1uwOYZ1i8EiqJSckdSV04hsLbsDyQSj3SgWbb9QeOJZoxdrfivzDt4kiVmziRvHo02ZFjOsHZ5Tjx4HtGavmZiz0QH3a6kUQzFEGpwDTqdx33IE0ZR4RDUw6sOD+3q6TK/MPNbdDBk7WQrO7FSsy6aX7fx~-1~-1~-1; utag_main=v_id:017e246687bc00126c9d1562327703073005906b00bd0$_sn:2$_se:6$_ss:0$_st:1641345911158$vapi_domain:ups.com$ses_id:1641343886098%3Bexp-session$_pn:2%3Bexp-session$_prevpage:ups%3Ahk%3Azh%3Atrack%3Bexp-1641347711220$_prevpageid:tracking%2FtrackWeb%2Ftra(3det).html%3Bexp-1641347711221; JSESSIONID=F39F2B4D825593C43CA7D1B67652C886; RT="z=1&dm=ups.com&si=b5824023-1117-491a-b068-3e58ad647000&ss=ky0trqja&sl=5&tt=1al4&bcn=%2F%2F684d0d48.akstat.io%2F&ld=9jof"; s_nr=1641344335440-Repeat; dayssincevisit=1641344335441',
            'origin': 'https://www.ups.com',
            'referer': 'https://www.ups.com/track?loc=zh_HK&tracknum={}&requester=WT/'.format(j),
            'sec-ch-ua': '"Chromium";v="92", " Not A;Brand";v="99", "Google Chrome";v="92"',
            'sec-ch-ua-mobile': '?0',
            'sec-fetch-dest': 'empty',
            'sec-fetch-mode': 'cors',
            'sec-fetch-site': 'same-origin',
            'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Safari/537.36',
            'x-xsrf-token': 'CfDJ8Jcj9GhlwkdBikuRYzfhrpIR_U6uCs7mVZuUDQyQxmOKoGcWSzavemYf-TclpDT7cIt8TWYU3W79qj0SFLpxl52i-U5ZE7jewsTxSpOh095ELOdrM8ruQ5wjMVn_g_QSlcmAuiKKCLkIa-yV1l70Hwk'
        }

        parm = {
            "Locale": "zh_HK",
            "TrackingNumber": ["{}".format(j)]
        }

        res = requests.post(url, headers=headers, json=parm).json()
        # print(res)
        for i in res["trackDetails"][0]["shipmentProgressActivities"]:
            print(i)
            times=i['date']+i['time']
            if i['activityScan']=='寄件人建立了標籤，但 UPS 尚未收到包裹。 ':
                title='已建立標籤'
            elif i['activityScan']=='您的包裹已經清關，正在派送途中。':
                title='進口清關'
            elif i['activityScan']=='在 UPS 設施處理中':
                title='在途中'
            elif i['activityScan']=='外出派送':
                title='派送途中'
            elif i['activityScan']=='已派送 ':
                title='已派送'
            else:
                title=''


            describes=i['activityScan']
            Tracking_number=j
            mysql_s(times, title, describes, Tracking_number)
        print('*' * 30)


if __name__ == '__main__':
    run()
